package com.sq.partner.manager.dao.impl.credit;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import org.apache.log4j.Logger;
import org.springframework.stereotype.Repository;

import com.sq.partner.manager.dao.credit.CreditProductDao;
import com.sq.partner.manager.dao.impl.BaseDaoImpl;
import com.sq.partner.manager.mybatis.model.CreditProduct;
import com.sq.partner.manager.util.DBUtil;
import com.sq.partner.manager.util.LogUtil;
import com.sq.partner.manager.util.Page;
import com.sq.partner.manager.util.SqlUtil;
import com.sq.partner.manager.util.StrUtil;

@Repository
public class CreditProductDaoImpl extends BaseDaoImpl implements CreditProductDao {

	private static Logger logger= LogUtil.getLog(CreditProductDaoImpl.class);
	@Override
	public boolean saveProd(CreditProduct credit) throws Exception {
		return this.add("insertCreditProd", credit);
	}

	@Override
	public boolean updateProd(CreditProduct credit) throws Exception {
		return this.update("updateCreditProdNotNull", credit);
	}

	@Override
	public boolean deleteProd(String prodCode) throws Exception {
		return this.delete("delCreditProdById", prodCode);
	}

	@Override
	public CreditProduct queryProdInfoById(String prodCode) throws SQLException {
		return (CreditProduct) this.query("queryCreditProdById", prodCode);
	}
	
	@Override
	public String findMaxCreditTradProdCode(String prodCode) throws SQLException {
		return   (String) this.query("queryMaxCreditTradProdCode",prodCode);
	}

	@Override
	public Page<CreditProduct> queryPage(Page<CreditProduct> page) throws SQLException {
		Connection conn = null;
		//查总条数
		PreparedStatement pstmt = null;
		//查分页
		PreparedStatement pstmt1 = null;
		ResultSet rs = null;
		try{
			conn = this.getConnection();
			StringBuffer sqlbuf=new StringBuffer();
			sqlbuf.append("SELECT T.PROD_CODE AS PRODCODE,T.PROD_NAME AS PRODNAME,T.PROD_DESC AS PRODDESC,T.PROD_TYPE AS PRODTYPE, "
					+ "T.STATUS AS STATUS, TO_CHAR(T.INSERT_TIME, 'yyyy-MM-dd HH24:MI:SS') AS INSERTTIME, "
					+ "NVL(T.IS_POOL, 'N') AS ISPOOL ,T.MAX_DELAY_DAY AS MAXDELAYDAY,T.MIN_DELAY_DAY AS  MINDELAYDAY,T.MAX_CANCEL_DAY AS MAXCANCELDAY,"
					+ "T.MAX_DAYS AS MAXDAYS,T.MIN_DAYS AS MINDAYS,T.AGENT_TYPE AS AGENTTYPE,T.PPP_UNIT AS PPPUNIT ,PROP.PROP_VALUE ||'-'||DICT.CODE_NAME epolicyCode "
					+ "FROM T_CREDIT_PROD T "
					+ "LEFT JOIN t_trad_prod_prop PROP  ON  T.Prod_Code=PROP.TRAD_PROD_CODE  AND PROP.PROP_TYPE='epolicy' "
					+ "LEFT JOIN T_GATEWAY_DICT DICT ON PROP.PROP_VALUE=DICT.CODE  AND DICT.CODE_TYPE='epolicyList' "
					+ "WHERE 1 = 1");
			String sql;
			List<String> list=new ArrayList<String>();
			List<Boolean> islike=new ArrayList<Boolean>();
			CreditProduct creditProd= page.getType();
			if(StrUtil.isNotEmpty(creditProd.getProdCode())){
				sqlbuf.append(" AND T.PROD_CODE like ?");
				list.add(creditProd.getProdCode());
				islike.add(true);
			}
			if(StrUtil.isNotEmpty(creditProd.getProdName())){
				sqlbuf.append(" AND T.PROD_NAME like ?");
				list.add(creditProd.getProdName());
				islike.add(true);
			}
			if(StrUtil.isNotEmpty(creditProd.getStatus())){
				sqlbuf.append(" AND T.STATUS=?");
				list.add(creditProd.getStatus());
				islike.add(false);
			}
			if(StrUtil.isNotEmpty(creditProd.getVstartDate())){
				sqlbuf.append(" AND T.INSERT_TIME >= to_date(? ,'yyyy-mm-dd')");
				list.add(creditProd.getVstartDate());
				islike.add(false);
			}
			if(StrUtil.isNotEmpty(creditProd.getVendDate())){
				sqlbuf.append(" AND T.INSERT_TIME  < to_date(? ,'yyyy-mm-dd') +1");
				list.add(creditProd.getVendDate());
				islike.add(false);
			}
			sqlbuf.append(" order by T.INSERT_TIME desc");
			sql=sqlbuf.toString();
			logger.debug("执行SQL：" + sql);

			pstmt = conn.prepareStatement("select count(*) from ("+sql+")");
			for (int i = 0; i < list.size(); i++) {
				if(islike.get(i)){
					pstmt.setObject(i+1, "%"+list.get(i)+"%");
				}else{
					pstmt.setObject(i+1, list.get(i));
				}
				
			}
			rs = pstmt.executeQuery();
			rs.next();
			Long total = rs.getLong(1);
			
			//查出分页
			page.setTotal(total);
			SqlUtil sqlUtil=new	SqlUtil();
			String pagesql=sqlUtil.buildPageSqlForOracle(sql,page).toString();
			logger.debug("执行SQL："+pagesql);
			pstmt1 = conn.prepareStatement(pagesql);
			for (int i = 0; i < list.size(); i++) {
				if(islike.get(i)){
					pstmt1.setObject(i+1, "%"+list.get(i)+"%");
				}else{
					pstmt1.setObject(i+1, list.get(i));
				}
			}
			rs = pstmt1.executeQuery();
			List lists = DBUtil.transRs2List(rs);
			page.setData(lists);
		}finally{
			close(rs);
			close(pstmt);
			close(pstmt1);
			close(conn);
		}
		
		return page;
	}

	@Override
	public List<CreditProduct> impCreditProd(Map<String, Object> map)
			throws Exception {
		return  this.queryList("impCreditProdByMap", map);
	}


}
